package com.coderman.dbant.utils;

import java.io.FileInputStream;
import java.sql.*;
import java.util.*;

/**
 * Created by ${fanchunshuai} on 2017-4-7.
 *
 * @version: V1.0
 * @Desc: JDBC简单工具类
 * @Copyright (c) 2017 58到家-版权所有
 */
public class JdbcUtils {

    private static Properties properties = new Properties();
    private static Connection connection = null;

    /**
     * 初始化DB连接
     * @param dbUrl
     * @param dbName
     * @param userName
     * @param password
     * @return
     * @throws Exception
     */
    public static Connection initDBConnection(String dbUrl,String dbName,String userName,String password)throws Exception{


        // 1. 准备获取连接的 4 个字符串: user, password, url, jdbcDriver
       /* String user = properties.getProperty("user");
        String password = properties.getProperty("password");*/
        String url= "jdbc:mysql://"+dbUrl+"/"+dbName;
        String jdbcDriver= "com.mysql.jdbc.Driver";

        // 2. 加载驱动: Class.forName(driverClass)

        Class.forName(jdbcDriver);
        // 3.获取数据库连接
        connection = DriverManager.getConnection(url, userName, password);
        return  connection;
    }


    /**
     * 执行单条语句
     * @param sql
     * @return
     * @throws Exception
     */
    public static boolean exeOneSql(String sql)throws Exception{
        if(connection == null){
            System.out.println("connection is null ,please init it....");
            return false;
        }
        //保存当前自动提交模式
        boolean autoCommit = connection.getAutoCommit();
        //关闭自动提交
        connection.setAutoCommit(false);
        connection.setAutoCommit(false);
        try {
            connection.prepareStatement(sql).execute();
            connection.commit();
            connection.setAutoCommit(autoCommit);
            return true;

        }catch (Exception e){
            connection.rollback();
            return false;
        }

    }

    /**
     * 批量执行多条语句
     * @param sqlList
     * @return
     * @throws Exception
     */
    public static boolean exeBatchSql(List<String> sqlList) throws Exception{
        try {
            if(connection == null){
                System.out.println("connection is null ,please init it ...");
                return false;
            }
            //保存当前自动提交模式
            boolean autoCommit=connection.getAutoCommit();
            //关闭自动提交
            connection.setAutoCommit(false);
            connection.setAutoCommit(false);
            Statement preparedStatement = connection.createStatement();

            for (String sql : sqlList){
                preparedStatement.addBatch(sql);
            }
            preparedStatement.executeBatch();
            connection.commit();
            connection.setAutoCommit(autoCommit);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            connection.rollback();
            return false;
        }
    }

    /**
     * 根据数据库字段生成bean
     * @param querySql
     * @return
     * @throws Exception
     */
    public static Map<String,List<String>> exeSearchSql(String querySql) throws Exception{
        try {
            if(connection == null){
                System.out.println("connection is null ,please init it ...");
                return null;
            }
            Statement preparedStatement = connection.createStatement();
            ResultSet resultSet = preparedStatement.executeQuery(querySql);
            ResultSetMetaData md = resultSet.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
            int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数
            Map<String,List<String>> rowData = new HashMap();


           while (resultSet.next()) {

                List<String> rowList =  rowData.get(resultSet.getObject(1));
                if(rowList == null){
                    rowList = new ArrayList<String>();
                    rowData.put(resultSet.getObject(1).toString(),rowList);
                }
                String rowInfo = resultSet.getObject(1).toString();
                for (int i = 2; i <= columnCount; i++) {
                    String s = resultSet.getObject(i).toString();
                    rowInfo = rowInfo+"-"+s;
                }
                System.out.println(rowList.size());
                rowList.add(rowInfo);
                rowData.put(resultSet.getObject(1).toString(),rowList);
            }
            return rowData;
        } catch (Exception e) {
            e.printStackTrace();
            connection.rollback();
            return null;
        }
    }


    /**
     * 根据sql查询数据
     * @param querySql
     * @return
     * @throws SQLException
     */
    public static List<Map<String,Object>>  selectDBList(String querySql) throws SQLException {
        Statement preparedStatement = connection.createStatement();
        ResultSet resultSet = preparedStatement.executeQuery(querySql);
        ResultSetMetaData md = resultSet.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
        int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数

        System.out.println("columnCount = "+columnCount);
        List<Map<String,Object>>  list = new ArrayList<>();

        // 遍历结果集
        while (resultSet.next()) {
            Map<String, Object> hm = new HashMap<String, Object>();
            for (int i = 0; i < columnCount; i++) {
                // 根据列索引取得每一列的列名,索引从1开始
                String columnName = md.getColumnName(i + 1);
                // 根据列名获得列值
                Object columnValue = resultSet.getObject(columnName);
                // 将列名作为key，列值作为值，放入 hm中，每个 hm相当于一条记录
                hm.put(columnName, columnValue);
                System.out.println(columnName+"--------------------"+columnValue);

            }
            // 将每个 hm添加到al中, al相当于是整个表，每个 hm是里面的一条记录
            list.add(hm);
        }
        System.out.println("list size = " + list.size());
        return list;
    }






   /* public static void main(String[] args) throws Exception {
        String sqlArray [] = new String[4];
        sqlArray[0] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        sqlArray[1] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        sqlArray[2] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        sqlArray[3] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        String sql1 = "SELECT TABLE_NAME,COLUMN_NAME,COLUMN_TYPE AS TYPE,COLUMN_COMMENT FROM information_schema.columns WHERE TABLE_SCHEMA = 'dbdj_authority_test'";

        //String sql = "delete from urls where id in (318657,318658,318659,318660,318661)";
        *//*JdbcUtils.initDBConnection("E:\\djworkspace\\openResource\\generateSqlBatchFromExcel\\generatesqlbatchfromexcel\\sqlBatchTools\\src\\main\\resources\\jdbc.properties");

        Map<String,List<String>> map = JdbcUtils.exeSearchSql(sql1);

        Iterator<Map.Entry<String,List<String>>> it =  map.entrySet().iterator();
        while (it.hasNext()){
            System.out.println(it.next().getValue().toString());
        }


        System.out.println("b = "+map.size());
        System.out.println("b = "+map.keySet().toString());
        //System.out.println("b = "+map.values().toString());

        //boolean b = JdbcUtils.exeBatchSql(sqlArray);
        //System.out.println("b = "+b);*//*
    }*/

}
